﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Jy123.BLL.Public;
using System.Data;

namespace Jy123.BLL.Guess
{
    public class GuessSql
    {

        /// <summary>
        /// 根据条件删除
        /// </summary>
        /// <param name="TableName">表名</param>
        /// <param name="strWhere">条件String例如"ID = 1"</param>
        /// <returns>影响行数</returns>
        public int DeleteByWhere(string TableName, string strWhere)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.AppendFormat("delete {0} where {1}", TableName, strWhere);

            return DBUtility.DbHelperSQL.ExecuteSql(strSql.ToString());
        }

        /// <summary>
        /// 判断是否存在数据
        /// </summary>
        /// <param name="TableName">表名</param>
        /// <param name="FGuess">竞猜ID</param>
        /// <returns>true/false</returns>
        public bool StatExists(string TableName, object FGuess)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.AppendFormat("select count(1) from {0} where FGuess in ({1})", TableName, FGuess);

            return DBUtility.DbHelperSQL.Exists(strSql.ToString());
        }

        /// <summary>
        /// 获取统计信息
        /// </summary>
        /// <param name="FGuess">竞猜ID</param>
        /// <returns>（人数/份数）</returns>
        public string GetStatHtml(object FGuess)
        {
            StringBuilder strSql = new StringBuilder();
            StringBuilder strSql2 = new StringBuilder();
            StringBuilder sbHtml = new StringBuilder();

            strSql.AppendFormat("select count(1) from User_Guess_Stat where FGuess in ({0})", FGuess);
            strSql2.AppendFormat("select Count(distinct FUser) from User_Guess_Stat where FGuess in ({0})", FGuess);

            sbHtml.AppendFormat("{0}人({1}份)",
                DBUtility.DbHelperSQL.GetSingle(strSql2.ToString()),
                DBUtility.DbHelperSQL.GetSingle(strSql.ToString()));

            return sbHtml.ToString();
        }


        /// <summary>
        /// 获取回复数
        /// </summary>
        /// <param name="FGuess">竞猜ID</param>
        /// <returns>回复数</returns>
        public string GetReviewCount(object FGuess)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.AppendFormat("select count(1) from User_Guess_Review where FGuess in ({0})", FGuess);

            return DBUtility.DbHelperSQL.GetSingle(strSql.ToString()).ToString();
        }

        /// <summary>
        /// 更新竞猜信息
        /// </summary>
        /// <param name="model">实体对象</param>
        /// <returns>影响行数</returns>
        public void UpdateGuess(string Title, string Account, string ID)
        {
            StringBuilder strSql = new StringBuilder();

            strSql.AppendFormat("update user_guess_main set Title = '{0}',Account = '{1}' where ID = {2}", Title, Account, ID);

            DBUtility.DbHelperSQL.ExecuteSql(strSql.ToString());
        }

        /// <summary>
        /// 根据用户ID获取排行榜列表
        /// </summary>
        /// <param name="UserID">用户ID</param>
        /// <param name="sortField">排序字段（"现金"或"猜中率"）</param>
        /// <returns>数据集</returns>
        public DataSet TopList(int UserID, string sortField)
        {
            StringBuilder sbSql = new StringBuilder();

            string strSql = @"SELECT   TOP (5) ID AS 用户ID, UserName AS 用户名, Portrait AS 头像地址, Cash AS 现金, (CASE WHEN
                    (SELECT   CONVERT(float, COUNT(User_Guess_Stat.FItem)) AS Expr1
                     FROM      User_Guess_Stat INNER JOIN
                                     User_Guess_Item ON User_Guess_Stat.FItem = User_Guess_Item.ID INNER JOIN
                                     User_Guess_Main ON User_Guess_Stat.FGuess = User_Guess_Main.ID
                     WHERE   (User_Guess_Stat.FUser = User_Main.ID) AND (User_Guess_Item.IsRight = 1) AND 
                                     (User_Guess_Main.IsCheckup = 1) AND (User_Guess_Main.OpenTime < GETDATE())) 
                = 0 THEN 0 ELSE
                    (SELECT   CONVERT(float, COUNT(User_Guess_Stat.FItem)) AS Expr1
                     FROM      User_Guess_Stat INNER JOIN
                                     User_Guess_Item ON User_Guess_Stat.FItem = User_Guess_Item.ID INNER JOIN
                                     User_Guess_Main ON User_Guess_Stat.FGuess = User_Guess_Main.ID
                     WHERE   (User_Guess_Stat.FUser = User_Main.ID) AND (User_Guess_Item.IsRight = 1) AND 
                                     (User_Guess_Main.IsCheckup = 1) AND (User_Guess_Main.OpenTime < GETDATE())) /
                    (SELECT   CONVERT(float, COUNT(User_Guess_Stat_2.FItem)) AS Expr1
                     FROM      User_Guess_Stat AS User_Guess_Stat_2 INNER JOIN
                                     User_Guess_Item AS User_Guess_Item_2 ON 
                                     User_Guess_Stat_2.FItem = User_Guess_Item_2.ID INNER JOIN
                                     User_Guess_Main AS User_Guess_Main_1 ON 
                                     User_Guess_Stat_2.FGuess = User_Guess_Main_1.ID
                     WHERE   (User_Guess_Stat_2.FUser = User_Main.ID) AND (User_Guess_Main_1.IsCheckup = 1) AND 
                                     (User_Guess_Main_1.OpenTime < GETDATE())) * 100 END) AS 猜中率
                    FROM      User_Main
                    WHERE   ((SELECT   COUNT(1) AS Expr1
                    FROM      User_Guess_Stat AS User_Guess_Stat_1 INNER JOIN
                                  User_Guess_Item AS User_Guess_Item_1 ON User_Guess_Stat_1.FItem = User_Guess_Item_1.ID
                    WHERE   (User_Guess_Stat_1.FUser = User_Main.ID)) > 0) AND (ID IN
                    (SELECT   FFriend
                     FROM      User_Friend
                     WHERE   (FUser = {0}))) OR
                     (ID = {1})
                    ORDER BY {2} DESC, {3} DESC";

            if (sortField.Equals("现金"))
            {
                sbSql.AppendFormat(strSql, UserID, UserID, sortField, "猜中率");
            }
            if (sortField.Equals("猜中率"))
            {
                sbSql.AppendFormat(strSql, UserID, UserID, sortField, "现金");
            }

            return DBUtility.DbHelperSQL.Query(sbSql.ToString());
        }

        /// <summary>
        /// 判断是否为正确项
        /// </summary>
        /// <param name="ItemID">竞猜项ID</param>
        /// <returns>true/false</returns>
        public Boolean CheckRight(int ItemID)
        {
            StringBuilder sbSql = new StringBuilder();

            string strSql = "select * from user_guess_item where id={0} and isright=1";

            sbSql.AppendFormat(strSql, ItemID); 

            return DBUtility.DbHelperSQL.Exists(sbSql.ToString());
        }

        /// <summary>
        /// 获取竞猜列表
        /// </summary>
        /// <param name="strWhere">筛选条件</param>
        /// <param name="cpage">起始页</param>
        /// <param name="psize">页记录大小</param>
        /// <param name="counts">获取数量</param>
        /// <returns>数据集</returns>
        public DataSet GetGuessList(string strWhere, int cpage, int psize, out List<int?> counts)
        {
            return Pagination.GetList(new Jy123.Model.MPagination()
            {
                Filter = (string.IsNullOrEmpty(strWhere)) ? null : strWhere,
                CurrentPage = cpage,
                Fields = "*",
                PageSize = psize,
                PrimaryKey = "ID",
                //Sort = "OverTime DESC",
                Tables = "User_Guess_Main"
            }, out counts); 
        }

        /// <summary>
        /// 竞猜结算
        /// </summary>
        /// <param name="guess">竞猜实体类</param>
        public void guessSettlement(Model.User_Guess_Main guess)
        {
            if (guess.FType == 1)
            {
                List<Jy123.Model.User_Guess_Stat> statList = new BLL.User_Guess_Stat().GetModelList("FGuess="+guess.ID);

                int sumPoint = statList.Count * guess.Point;//竞猜总现金

                int rightCount = getRightCount(guess.ID);//竞猜正确次数

                if (rightCount != 0)
                {
                    int Point = sumPoint / rightCount;

                    foreach (Jy123.Model.User_Guess_Stat stat in statList)
                    {
                        if (CheckRight(stat.FItem))
                        {
                            Jy123.Model.User_Main rightuser = new BLL.User_Main().GetModel(stat.FUser);

                            rightuser.Cash = rightuser.Cash + Point;//正确用户现金回馈

                            new BLL.User_Main().Update(rightuser);
                        }
                    }
                }
                guess.IsSettlement = true;//变更竞猜结算状态

                new BLL.User_Guess_Main().Update(guess);

                Model.User_Main user = new BLL.User_Main().GetModel(guess.FUser);

                new BLL.Public.State().CreateGuessState(user,null,guess,6);//添加动态 发布竞猜结果

                
            }
            if (guess.FType == 2)
            {
                List<Jy123.Model.User_Guess_Stat> statList = new BLL.User_Guess_Stat().GetModelList("FGuess=" + guess.ID);
                
                int Point = guess.Point*guess.Multiple;//每份竞应得返还现金

                int sumPoint = guess.SumPoint;//预付现金
                foreach (Jy123.Model.User_Guess_Stat stat in statList)
                {
                    if (CheckRight(stat.FItem))//如果正确
                    {
                        Jy123.Model.User_Main rightuser = new BLL.User_Main().GetModel(stat.FUser);

                        rightuser.Cash = rightuser.Cash + Point;//正确用户结算

                        new BLL.User_Main().Update(rightuser);

                        sumPoint = sumPoint - Point;//预付金扣除竞猜正确奖金
                    }
                    else
                    {
                        sumPoint = sumPoint + guess.Point;//预付金加上竞猜不正确的金额
                    }
                }
                guess.IsSettlement = true;//变更竞猜结算状态

                new BLL.User_Guess_Main().Update(guess);

                Model.User_Main user = new BLL.User_Main().GetModel(guess.FUser);

                user.Cash = user.Cash + sumPoint;//庄家应得结算

                new BLL.User_Main().Update(user);

                new BLL.Public.State().CreateGuessState(user, null, guess, 6);//添加动态 发布竞猜结果
            }
        }

        /// <summary>
        /// 竞猜正确的人数
        /// </summary>
        /// <param name="GuessID">竞猜ID</param>
        /// <returns>true/false</returns>
        public int getRightCount(int GuessID)
        {
            StringBuilder sbSql = new StringBuilder();

            string strSql = "select count(1) from user_guess_stat where fguess={0} and fitem =(select id from user_guess_item where fguess={1} and isright=1)";

            sbSql.AppendFormat(strSql, GuessID, GuessID);

            return Convert.ToInt16(DBUtility.DbHelperSQL.GetSingle(sbSql.ToString()));
        }
    }
}